﻿# -*- coding: UTF-8 -*-
# @Time :2021/12/1 9:29
# @Author :Liuzheng
# @Email :1540234613@qq.com

from pywebio.input import *
from pywebio.output import *
from pywebio import start_server, input, output
import pandas as pd
from pyecharts import options as opts
from pyecharts.charts import Bar, Pie
from sqlalchemy import create_engine
import pymysql
import json
"""
1、连接本地数据库对数据进行统计分析
2、对上述结果调用pywebio、进行界面化处理
3、运用pyecharts、对结果进行展示
4、docker部署服务器
"""


from fastapi import FastAPI

app = FastAPI()

def forceupdate():
    json = "{'cnt':'1、加快模块加载速度\n2、修复部分已知问题\n3、增加蓝牙应用模块','url':'http://visionintelli.cn:8886/apks/findroid_1.0.2.4_0310_ble.apk','title':'发现新版本:1.0.2.4','target':'4','force':'false'}";
    return json

def list(params:str = ""):
    try:
        host='127.0.0.1'  # 连接名称，默认127.0.0.1
        user='chenliang'  # 用户名
        passwd='chenliang123!'  # 密码
        port= '3306'  # 端口，默认为3306
        db='androidfragment'  # 数据库名称
        charset='utf8'  # 字符编码
        # conn = pymysql.connect(
        # )
        engine = create_engine('mysql+pymysql://%s:%s@%s:%s/%s'
                             %(user,passwd,host,port,db))
        if params == 'shushijia':
            sql = "select * from fragment where unvisible is not null and unvisible not like '' and unvisible like '" + params + "' order by id desc;"
        else:
            sql = "select * from fragment where unvisible is null or unvisible like '' or unvisible like '"+params+"' order by id desc;"
        #
        # select * from blog_type;
        df = pd.read_sql(sql, engine.connect())

        pd.set_option('display.unicode.ambiguous_as_wide', True)
        pd.set_option('display.unicode.east_asian_width', True)
        print(df)

    except OSError as reason:
        print('出错原因是%s' % str(reason))


    return  df.to_json(orient='records')
    #
    # #性别比例
    # sql2 = """
    #     select 性别,count(1) as 数量 from liuzheng.2021年蓟州区普高录取结果信息表  GROUP BY 性别
    #     """
    # data2 = pd.read_sql_query(sql2, conn)
    # xb=list(data2['性别'])
    # xb_sl=list(data2['数量'])
    #
    # c = (
    #     Pie()
    #         .add("", [list(z) for z in zip(xb, xb_sl)])
    #         .set_global_opts(title_opts=opts.TitleOpts(title="2021年蓟州区普高录取男女比例"))
    #         .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
    #
    # )
    #
    # c.width = "100%"
    # put_html(c.render_notebook())
    #
    # output.put_markdown(f'**分析结果：从图中可以看出男生人数为{xb_sl[1]}，女生人数为{xb_sl[0]}**')

import os.path
from pathlib import Path

from starlette.responses import FileResponse,PlainTextResponse


@app.get('/apks/{file_name}')
async def download(file_name: str):
    f = os.path.join('C:/Users/Administrator/apps/findroid/apks/', file_name)
    print(Path(f).name)

    fr = FileResponse(
        path=f,
        filename=Path(f).name,
    )

    return fr
import datetime
@app.get('/test?p=error&message={message}')
async def error(message: str):
    try:
        host='159.75.109.121'  # 连接名称，默认127.0.0.1
        user='chenliang'  # 用户名
        passwd='chenliang123!'  # 密码
        port= '3306'  # 端口，默认为3306
        db='androidfragment'  # 数据库名称
        charset='utf8'  # 字符编码
        # conn = pymysql.connect(
        # )
        conn = create_engine('mysql+pymysql://%s:%s@%s:%s/%s'
                             %(user,passwd,host,port,db))

        try:
            # 步骤2：通过连接对象获取游标对象 ---> Cursor
            with conn.cursor() as cursor:
                # 步骤3：通过游标对象向数据库发出SQL并获取执行结果
                affected_rows = cursor.execute(
                    'insert into error (message, time) values (%s,%s)',
                    (message, dt.strftime('%Y-%m-%d %H:%M:%S'))

                )
                if affected_rows == 1:
                    print('添加错误日志成功！！！')
            # 步骤4：操作成功提交（让之前的操作生效）
            conn.commit()
        except pymysql.MySQLError as err:
            print(err)
            # 步骤4：操作失败回滚（撤销之前的操作）
            conn.rollback()
        finally:
            # 步骤5：关闭连接，释放资源
            conn.close()



    except OSError as reason:
        print('出错原因是%s' % str(reason))



@app.get('/txt/{file_name}')
async def download(file_name: str):
    f = os.path.join('C:/Users/Administrator/apps/findroid/txt/', file_name)
    print(Path(f).name)

    file_object = open(Path(f), 'r', encoding="utf-8")  # 创建一个文件对象，也是一个可迭代对象
    try:
        all_the_text = file_object.read()  # 结果为str类型
        fr = PlainTextResponse(
            all_the_text
        )
    finally:
        file_object.close()


    return fr


@app.get('/test')
def main(p:str = "", params:str = ""):
    if p == "list":
        return list(params)
    elif p == "forceupdate":
        return forceupdate()

#我画你猜
# 查看图片
@app.get("/image/{imageId}")
def get_image(imageId):
    # 图片上传保存的路径
    path = 'C:/Users/Administrator/apps/findroid/image/{}.jpg'.format(imageId)
    with open(path, 'rb') as f:
        resp = FileResponse(path)
        return resp

# 展示列表
@app.get("/images/{tag}")
def list_images(tag:str = ""):
    if tag == "all":
        tag = ""
    try:
        host='127.0.0.1'  # 连接名称，默认127.0.0.1
        user='chenliang'  # 用户名
        passwd='chenliang123!'  # 密码
        port= '3306'  # 端口，默认为3306
        db='wohuanicai'  # 数据库名称
        charset='utf8'  # 字符编码
        # conn = pymysql.connect(
        # )
        engine2 = create_engine('mysql+pymysql://%s:%s@%s:%s/%s'
                             %(user,passwd,host,port,db))
        sql = "select * from image where tag is null or tag like '' or tag like '%"+tag+"%' order by Id desc;"
        #
        # select * from blog_type;
        df = pd.read_sql(sql, engine2.connect())

        pd.set_option('display.unicode.ambiguous_as_wide', True)
        pd.set_option('display.unicode.east_asian_width', True)
        print(df)

    except OSError as reason:
        print('出错原因是%s' % str(reason))

    return  df.to_json(orient='records')

if __name__ == '__main__':
    import uvicorn

    uvicorn.run(app=app,
                host="0.0.0.0",
                port=8886,
                workers=1)

# if __name__ == '__main__':
#     start_server(main, port=8090)

